package com.apes.fico.finance.respository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.DateUtil;
import org.springframework.stereotype.Component;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Objects;

/**
 * Created by lishikun on 2021/1/29 16:23
 */
@Component
public class StaffSalariesDao extends ApesDao {

    /**
     * 批量查询交易流水号
     * @param param
     * @return
     */
    public JSONArray queryAllTransactionFlow(JSONObject param,String company) {
        JSONObject request = new JSONObject();
        String sql = "SELECT a.dept_id,to_char(a.year_month,'yyyy-mm') year_month,\n" +
                " a.person_id,a.type,a.post_group_id,a.base_amt,a.tc_total,a.sgtc,a.xstc,a.earnings_tax,\n" +
                "a.subsidy_amt,a.rewar_punishment_amt,a.social_security_company_amt,a.electricity," +
                "a.social_security_person_amt,a.accumulation_fund_company_amt,a.company_id,a.withhold,\n" +
                "a.accumulation_fund_person_amt,a.wages_payable_amt,a.net_salary_amt,a.summary,a.account,a.repayment,a.valid,a.id\n" +
                " FROM gl_staff_salaries a where 1=1 " ;
        if (Objects.nonNull(param.get("yearMonth"))) {
            String yearMonth = param.getString("yearMonth");
            sql += " AND to_char(a.year_month,'yyyy-mm') = substr('"+ yearMonth +"',0,7)";
        }

        if (Objects.nonNull(param.get("dept"))) {
            JSONArray dept = param.getJSONArray("dept");
            dept.forEach(dept1->{
                request.put("dept", ((JSONObject) dept1).get("id"));
            });
            sql += " and a.dept_id = :dept ";
        }

        if (Objects.nonNull(param.get("type"))) {
            request.put("type", param.getString("type"));
            sql += " and a.type = :type ";
        }

        if (!"A0000".equals(company)) {
            sql += " AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.dept_company_id = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))";
        }
        return toJSONArray(find(sql, request));
    }
}
